using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SouthernLand.Common.BOL.NhaDat;
using SouthernLand.Common.Untilities;

namespace SouthernLand.Common.DAL.NhaDat
{
    public class RaovatDAL : BaseDAL
    {
        #region ObjIDataReader

        /// <summary>
        /// Returns a new Raovat instance filled with the DataReader's current record data 
        /// </summary>
        private Raovat GetRaovatFromReader(IDataReader dr, bool getTotalRecords)
        {
            var obj = new Raovat();
            obj.RaovatID = (dr["RaovatID"] is DBNull) ? Int32.MinValue : (Int32) dr["RaovatID"];
            obj.UserID = (dr["UserID"] is DBNull) ? String.Empty : Convert.ToString(dr["UserID"]);
            obj.CateID = (dr["CateID"] is DBNull) ? Int32.MinValue : (Int32) dr["CateID"];
            obj.Loaitin = (dr["Loaitin"] is DBNull) ? Int32.MinValue : (Int32) dr["Loaitin"];
            obj.Tinhthanh = (dr["Tinhthanh"] is DBNull) ? Int32.MinValue : (Int32) dr["Tinhthanh"];
            obj.Quanhuyen = (dr["Quanhuyen"] is DBNull) ? Int32.MinValue : (Int32) dr["Quanhuyen"];
            obj.DiachiNha = (dr["DiachiNha"] is DBNull) ? String.Empty : (String) dr["DiachiNha"];
            obj.DientichDat = (dr["DientichDat"] is DBNull) ? Double.MinValue : (Double) dr["DientichDat"];
            obj.DientichXD = (dr["DientichXD"] is DBNull) ? Double.MinValue : (Double) dr["DientichXD"];
            obj.DientichSD = (dr["DientichSD"] is DBNull) ? Double.MinValue : (Double) dr["DientichSD"];
            obj.Solau = (dr["Solau"] is DBNull) ? Int32.MinValue : (Int32) dr["Solau"];
            obj.PhongNgu = (dr["PhongNgu"] is DBNull) ? Int32.MinValue : (Int32) dr["PhongNgu"];
            obj.PhongVS = (dr["PhongVS"] is DBNull) ? Int32.MinValue : (Int32) dr["PhongVS"];
            obj.TinhtrangPL = (dr["TinhtrangPL"] is DBNull) ? Int32.MinValue : (Int32) dr["TinhtrangPL"];
            obj.HuongNha = (dr["HuongNha"] is DBNull) ? Int32.MinValue : (Int32) dr["HuongNha"];
            obj.Vitri = (dr["Vitri"] is DBNull) ? String.Empty : (String) dr["Vitri"];
            obj.DuongtruocNha = (dr["DuongtruocNha"] is DBNull) ? Int32.MinValue : (Int32) dr["DuongtruocNha"];
            obj.Gia = (dr["Gia"] is DBNull) ? Decimal.MinValue : (Decimal) dr["Gia"];
            obj.DonVT = (dr["DonVT"] is DBNull) ? Int32.MinValue : (Int32) dr["DonVT"];
            obj.GiaTren = (dr["GiaTren"] is DBNull) ? String.Empty : (String) dr["GiaTren"];
            obj.Tieude = (dr["Tieude"] is DBNull) ? String.Empty : (String) dr["Tieude"];
            obj.Mota = (dr["Mota"] is DBNull) ? String.Empty : (String) dr["Mota"];
            obj.Hinh1 = (dr["Hinh1"] is DBNull) ? String.Empty : (String) dr["Hinh1"];
            obj.Hinh2 = (dr["Hinh2"] is DBNull) ? String.Empty : (String) dr["Hinh2"];
            obj.Hinh3 = (dr["Hinh3"] is DBNull) ? String.Empty : (String) dr["Hinh3"];
            obj.Hinh4 = (dr["Hinh4"] is DBNull) ? String.Empty : (String) dr["Hinh4"];
            obj.Hinh5 = (dr["Hinh5"] is DBNull) ? String.Empty : (String) dr["Hinh5"];
            obj.Hoten = (dr["Hoten"] is DBNull) ? String.Empty : (String) dr["Hoten"];
            obj.Diachi = (dr["Diachi"] is DBNull) ? String.Empty : (String) dr["Diachi"];
            obj.Email = (dr["Email"] is DBNull) ? String.Empty : (String) dr["Email"];
            obj.Dienthoai = (dr["Dienthoai"] is DBNull) ? String.Empty : (String) dr["Dienthoai"];
            obj.Didong = (dr["Didong"] is DBNull) ? String.Empty : (String) dr["Didong"];
            obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue : (DateTime) dr["AddedDate"];
            obj.ExpireDate = (dr["ExpireDate"] is DBNull) ? DateTime.MinValue : (DateTime) dr["ExpireDate"];
            obj.DisplayOrder = (dr["DisplayOrder"] is DBNull) ? Int32.MinValue : (Int32) dr["DisplayOrder"];
            obj.IsActive = (dr["IsActive"] is DBNull) ? false : (Boolean) dr["IsActive"];
            obj.TotalViews = (dr["TotalViews"] is DBNull) ? Int32.MinValue : (Int32) dr["TotalViews"];

            if (getTotalRecords)
                obj.TotalRecords = (dr["TotalRecords"] is DBNull) ? -1 : ConvertUtility.ToInt32(dr["TotalRecords"]);

            return obj;
        }

        #endregion

        /// <summary>
        /// Returns a collection of Raovat with the data read from the input DataReader
        /// </summary>
        private List<Raovat> GetRaovatCollectionFromReader(IDataReader dr, bool getTotalRecords)
        {
            var obj = new List<Raovat>();
            while (dr.Read())
                obj.Add(GetRaovatFromReader(dr, getTotalRecords));
            return obj;
        }

        public int Insert(Raovat obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@RaovatID", SqlDbType.Int).Direction = ParameterDirection.Output;
                myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = obj.UserID;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                myCommand.Parameters.Add("@Loaitin", SqlDbType.Int).Value = obj.Loaitin;
                myCommand.Parameters.Add("@Tinhthanh", SqlDbType.Int).Value = obj.Tinhthanh;
                myCommand.Parameters.Add("@Quanhuyen", SqlDbType.Int).Value = obj.Quanhuyen;
                myCommand.Parameters.Add("@DiachiNha", SqlDbType.NVarChar).Value = obj.DiachiNha;
                myCommand.Parameters.Add("@DientichDat", SqlDbType.Float).Value = obj.DientichDat;
                myCommand.Parameters.Add("@DientichXD", SqlDbType.Float).Value = obj.DientichXD;
                myCommand.Parameters.Add("@DientichSD", SqlDbType.Float).Value = obj.DientichSD;
                myCommand.Parameters.Add("@Solau", SqlDbType.Int).Value = obj.Solau;
                myCommand.Parameters.Add("@PhongNgu", SqlDbType.Int).Value = obj.PhongNgu;
                myCommand.Parameters.Add("@PhongVS", SqlDbType.Int).Value = obj.PhongVS;
                myCommand.Parameters.Add("@TinhtrangPL", SqlDbType.Int).Value = obj.TinhtrangPL;
                myCommand.Parameters.Add("@HuongNha", SqlDbType.Int).Value = obj.HuongNha;
                myCommand.Parameters.Add("@Vitri", SqlDbType.NVarChar).Value = obj.Vitri;
                myCommand.Parameters.Add("@DuongtruocNha", SqlDbType.Int).Value = obj.DuongtruocNha;
                myCommand.Parameters.Add("@Gia", SqlDbType.Money).Value = obj.Gia;
                myCommand.Parameters.Add("@DonVT", SqlDbType.Int).Value = obj.DonVT;
                myCommand.Parameters.Add("@GiaTren", SqlDbType.NVarChar).Value = obj.GiaTren;
                myCommand.Parameters.Add("@Tieude", SqlDbType.NVarChar).Value = obj.Tieude;
                myCommand.Parameters.Add("@Mota", SqlDbType.NVarChar).Value = obj.Mota;
                myCommand.Parameters.Add("@Hinh1", SqlDbType.NVarChar).Value = obj.Hinh1;
                myCommand.Parameters.Add("@Hinh2", SqlDbType.NVarChar).Value = obj.Hinh2;
                myCommand.Parameters.Add("@Hinh3", SqlDbType.NVarChar).Value = obj.Hinh3;
                myCommand.Parameters.Add("@Hinh4", SqlDbType.NVarChar).Value = obj.Hinh4;
                myCommand.Parameters.Add("@Hinh5", SqlDbType.NVarChar).Value = obj.Hinh5;
                myCommand.Parameters.Add("@Hoten", SqlDbType.NVarChar).Value = obj.Hoten;
                myCommand.Parameters.Add("@Diachi", SqlDbType.NVarChar).Value = obj.Diachi;
                myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
                myCommand.Parameters.Add("@Dienthoai", SqlDbType.VarChar).Value = obj.Dienthoai;
                myCommand.Parameters.Add("@Didong", SqlDbType.VarChar).Value = obj.Didong;
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@ExpireDate", SqlDbType.DateTime).Value = obj.ExpireDate;
                myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;
                myCommand.Parameters.Add("@TotalViews", SqlDbType.Int).Value = obj.TotalViews;

                int ret = myCommand.ExecuteNonQuery();
                return (int) myCommand.Parameters["@RaovatID"].Value;
            }
        }

        public bool Update(Raovat obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@RaovatID", SqlDbType.Int).Value = obj.RaovatID;
                myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = obj.UserID;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                myCommand.Parameters.Add("@Loaitin", SqlDbType.Int).Value = obj.Loaitin;
                myCommand.Parameters.Add("@Tinhthanh", SqlDbType.Int).Value = obj.Tinhthanh;
                myCommand.Parameters.Add("@Quanhuyen", SqlDbType.Int).Value = obj.Quanhuyen;
                myCommand.Parameters.Add("@DiachiNha", SqlDbType.NVarChar).Value = obj.DiachiNha;
                myCommand.Parameters.Add("@DientichDat", SqlDbType.Float).Value = obj.DientichDat;
                myCommand.Parameters.Add("@DientichXD", SqlDbType.Float).Value = obj.DientichXD;
                myCommand.Parameters.Add("@DientichSD", SqlDbType.Float).Value = obj.DientichSD;
                myCommand.Parameters.Add("@Solau", SqlDbType.Int).Value = obj.Solau;
                myCommand.Parameters.Add("@PhongNgu", SqlDbType.Int).Value = obj.PhongNgu;
                myCommand.Parameters.Add("@PhongVS", SqlDbType.Int).Value = obj.PhongVS;
                myCommand.Parameters.Add("@TinhtrangPL", SqlDbType.Int).Value = obj.TinhtrangPL;
                myCommand.Parameters.Add("@HuongNha", SqlDbType.Int).Value = obj.HuongNha;
                myCommand.Parameters.Add("@Vitri", SqlDbType.NVarChar).Value = obj.Vitri;
                myCommand.Parameters.Add("@DuongtruocNha", SqlDbType.Int).Value = obj.DuongtruocNha;
                myCommand.Parameters.Add("@Gia", SqlDbType.Money).Value = obj.Gia;
                myCommand.Parameters.Add("@DonVT", SqlDbType.Int).Value = obj.DonVT;
                myCommand.Parameters.Add("@GiaTren", SqlDbType.NVarChar).Value = obj.GiaTren;
                myCommand.Parameters.Add("@Tieude", SqlDbType.NVarChar).Value = obj.Tieude;
                myCommand.Parameters.Add("@Mota", SqlDbType.NVarChar).Value = obj.Mota;
                myCommand.Parameters.Add("@Hinh1", SqlDbType.NVarChar).Value = obj.Hinh1;
                myCommand.Parameters.Add("@Hinh2", SqlDbType.NVarChar).Value = obj.Hinh2;
                myCommand.Parameters.Add("@Hinh3", SqlDbType.NVarChar).Value = obj.Hinh3;
                myCommand.Parameters.Add("@Hinh4", SqlDbType.NVarChar).Value = obj.Hinh4;
                myCommand.Parameters.Add("@Hinh5", SqlDbType.NVarChar).Value = obj.Hinh5;
                myCommand.Parameters.Add("@Hoten", SqlDbType.NVarChar).Value = obj.Hoten;
                myCommand.Parameters.Add("@Diachi", SqlDbType.NVarChar).Value = obj.Diachi;
                myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
                myCommand.Parameters.Add("@Dienthoai", SqlDbType.VarChar).Value = obj.Dienthoai;
                myCommand.Parameters.Add("@Didong", SqlDbType.VarChar).Value = obj.Didong;
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@ExpireDate", SqlDbType.DateTime).Value = obj.ExpireDate;
                myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;
                myCommand.Parameters.Add("@TotalViews", SqlDbType.Int).Value = obj.TotalViews;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public bool Delete(Int32 raovatId)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@RaovatID", SqlDbType.Int).Value = raovatId;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public bool GiaHan(Int32 raovatId, int soNgay)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GiaHan", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@RaovatID", SqlDbType.Int).Value = raovatId;
                myCommand.Parameters.Add("@SoNgay", SqlDbType.Int).Value = soNgay;
                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }


        public List<Raovat> Search(eTrangthai trangthai, string keyword, Raovat objRaovat, string strWhereGia, int donVt,
                                   string strWhereDienTichDat, string sort, int pageIndex, int pageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_GetTableList", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;

                string Table = "";
                if (trangthai == eTrangthai.TinVip)
                    Table = "vw_w4u_Raovat_TinVip";
                else if (trangthai == eTrangthai.HetHan)
                    Table = "vw_w4u_Raovat_HetHan";
                else
                    Table = "vw_w4u_Raovat_HieuLuc";


                myCommand.Parameters.Add("@Table", SqlDbType.NVarChar).Value = Table;
                myCommand.Parameters.Add("@Fields", SqlDbType.NVarChar).Value = Table + ".*";
                myCommand.Parameters.Add("@PK", SqlDbType.NVarChar).Value = "RaovatID";

                string strWhere = " (RaovatID IS Not NULL )";
                if (keyword.Trim().Length > 0)
                    strWhere = strWhere + " And((RaovatID = " + ConvertUtility.ToInt32(keyword) + ") " +
                               "Or (Tieude Like N'%" + keyword + "%') " +
                               "Or (Mota Like N'%" + keyword + "') )";

                if (objRaovat.Loaitin > 0)
                    strWhere = strWhere + " And (Loaitin = " + objRaovat.Loaitin + ") ";

                if (objRaovat.CateID > 0)
                    strWhere = strWhere + " And (CateID = " + objRaovat.CateID + ") ";

                if (objRaovat.Tinhthanh > 0)
                    strWhere = strWhere + " And (TinhThanh = " + objRaovat.Tinhthanh + ") ";

                if (objRaovat.Quanhuyen > 0)
                    strWhere = strWhere + " And (Quanhuyen = " + objRaovat.Quanhuyen + ") ";

                if (objRaovat.HuongNha > 0)
                    strWhere = strWhere + " And (HuongNha = " + objRaovat.HuongNha + ") ";

                if (objRaovat.TinhtrangPL > 0)
                    strWhere = strWhere + " And (TinhTrangPL = " + objRaovat.TinhtrangPL + ") ";

                if (strWhereGia.Trim().Length > 0)
                {
                    strWhere = strWhere + " And (" + strWhereGia + ") ";

                    if (donVt > 0)
                        strWhere = strWhere + " And (DonVT = " + donVt + ") ";
                }


                if (strWhereDienTichDat.Trim().Length > 0)
                    strWhere = strWhere + " And (" + strWhereDienTichDat + ") ";


                myCommand.Parameters.Add("@Where", SqlDbType.NVarChar).Value = strWhere;

                if (sort.Trim().Length > 0)
                    myCommand.Parameters.Add("@Sort", SqlDbType.NVarChar).Value = sort;
                else
                    myCommand.Parameters.Add("@Sort", SqlDbType.NVarChar).Value = "AddedDate Desc";

                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;

                SqlDataReader dr = myCommand.ExecuteReader();
                return GetRaovatCollectionFromReader(dr, true);
            }
        }

        /// <summary>
        /// Retrieves the w4u_Raovat with the specified ID
        /// </summary>
        public Raovat GetRaovatByID(Int32 raovatId)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GetRaovatByID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@RaovatID", SqlDbType.Int).Value = raovatId;

                SqlDataReader dr = myCommand.ExecuteReader();
                return (dr.Read() ? GetRaovatFromReader(dr, false) : null);
            }
        }

        //Get Raovat moi nhat theo LoaitinID
        public List<Raovat> GetRaovatMoiNhat(int loaitinId)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GetRaovatMoiNhat", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@LoaitinID", SqlDbType.Int).Value = loaitinId;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetRaovatCollectionFromReader(dr, false);
            }
        }

        //Get All Raovat
        public List<Raovat> GetAllRaovat(int pageIndex, int pageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GetAllRaovat", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetRaovatCollectionFromReader(dr, false);
            }
        }

        // Get Count All records of Table  Raovat
        public int GetCountRaovat()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GetCountRaovat", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int) myCommand.ExecuteScalar();
            }
        }

        // Get RaovatByLoaitin
        public List<Raovat> GetRaovatDaLuu(String UserID, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GetRaovatDaLuu", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetRaovatCollectionFromReader(dr, false);
            }
        }

        public List<Raovat> GetRaovatHethan(string strSort, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                if (strSort.Length < 3)
                    strSort = "RaovatID Desc";

                var myCommand = new SqlCommand("w4u_GetTableList", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Table", SqlDbType.NVarChar).Value = "vw_w4u_Raovat_HetHan";
                myCommand.Parameters.Add("@PK", SqlDbType.NVarChar).Value = "RaovatID";
                myCommand.Parameters.Add("@Fields", SqlDbType.NVarChar).Value = "vw_w4u_Raovat_HetHan.*";
                myCommand.Parameters.Add("@Where", SqlDbType.NVarChar).Value = "(RaovatID IS NOT NULL)";
                myCommand.Parameters.Add("@Sort", SqlDbType.NVarChar).Value = strSort;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetRaovatCollectionFromReader(dr, true);
            }
        }

        public List<Raovat> GetAllRaovat2(string strSort, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                if (strSort.Length < 3)
                    strSort = "RaovatID Desc";

                var myCommand = new SqlCommand("w4u_GetTableList", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Table", SqlDbType.NVarChar).Value = "vw_w4u_Raovat_HieuLuc";
                myCommand.Parameters.Add("@PK", SqlDbType.NVarChar).Value = "RaovatID";
                myCommand.Parameters.Add("@Fields", SqlDbType.NVarChar).Value = "vw_w4u_Raovat_HieuLuc.*";
                myCommand.Parameters.Add("@Where", SqlDbType.NVarChar).Value = "(RaovatID IS NOT NULL)";
                myCommand.Parameters.Add("@Sort", SqlDbType.NVarChar).Value = strSort;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetRaovatCollectionFromReader(dr, true);
            }
        }

        // Get RaovatByUserID
        public List<Raovat> GetRaovatHetHanByUser(String UserID, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_GetTableList", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Table", SqlDbType.NVarChar).Value = "vw_w4u_Raovat_HetHan";
                myCommand.Parameters.Add("@PK", SqlDbType.NVarChar).Value = "RaovatID";
                myCommand.Parameters.Add("@Fields", SqlDbType.NVarChar).Value = "vw_w4u_Raovat_HetHan.*";
                myCommand.Parameters.Add("@Where", SqlDbType.VarChar).Value = "UserID = '" + UserID.Trim() + "'";
                myCommand.Parameters.Add("@Sort", SqlDbType.NVarChar).Value = "RaovatID Desc";
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetRaovatCollectionFromReader(dr, true);
            }
        }

        // Get RaovatByUserID
        public List<Raovat> GetRaovatByUserID(String UserID, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_GetTableList", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@Table", SqlDbType.NVarChar).Value = "vw_w4u_Raovat_HieuLuc";
                myCommand.Parameters.Add("@PK", SqlDbType.NVarChar).Value = "RaovatID";
                myCommand.Parameters.Add("@Fields", SqlDbType.NVarChar).Value = "vw_w4u_Raovat_HieuLuc.*";
                myCommand.Parameters.Add("@Where", SqlDbType.NVarChar).Value = "UserID = '" + UserID.Trim() + "'";
                myCommand.Parameters.Add("@Sort", SqlDbType.NVarChar).Value = "RaovatID Desc";
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetRaovatCollectionFromReader(dr, true);
            }
        }

        // Get RaovatByCateID
        public List<Raovat> GetRaovatByCateID(Int32 CateID, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_GetTableList", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@Table", SqlDbType.NVarChar).Value = "vw_w4u_Raovat_HieuLuc";
                myCommand.Parameters.Add("@PK", SqlDbType.NVarChar).Value = "RaovatID";
                myCommand.Parameters.Add("@Fields", SqlDbType.NVarChar).Value = "vw_w4u_Raovat_HieuLuc.*";
                myCommand.Parameters.Add("@Where", SqlDbType.NVarChar).Value = "CateID = " + CateID;
                myCommand.Parameters.Add("@Sort", SqlDbType.NVarChar).Value = "RaovatID Desc";
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetRaovatCollectionFromReader(dr, true);
            }
        }

        // Get RaovatByLoaitin
        public List<Raovat> GetRaovatByLoaitin(Int32 Loaitin, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GetRaovatByLoaitin", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Loaitin", SqlDbType.Int).Value = Loaitin;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetRaovatCollectionFromReader(dr, false);
            }
        }

        // Get RaovatByTinhthanh
        public List<Raovat> GetRaovatByTinhthanh(Int32 Tinhthanh, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GetRaovatByTinhthanh", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Tinhthanh", SqlDbType.Int).Value = Tinhthanh;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetRaovatCollectionFromReader(dr, false);
            }
        }

        // Get RaovatCountByUserID
        public int GetRaovatCountByUserID(String userId)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GetRaovatCountByUserID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = userId;
                return (int) myCommand.ExecuteScalar();
            }
        }

        // Get RaovatCountByCateID
        public int GetRaovatCountByCateID(Int32 CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GetRaovatCountByCateID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
                return (int) myCommand.ExecuteScalar();
            }
        }

        // Get RaovatCountByLoaitin
        public int GetRaovatCountByLoaitin(Int32 Loaitin)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GetRaovatCountByLoaitin", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Loaitin", SqlDbType.Int).Value = Loaitin;
                return (int) myCommand.ExecuteScalar();
            }
        }

        // Get RaovatCountByTinhthanh
        public int GetRaovatCountByTinhthanh(Int32 Tinhthanh)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Raovat_GetRaovatCountByTinhthanh", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Tinhthanh", SqlDbType.Int).Value = Tinhthanh;
                return (int) myCommand.ExecuteScalar();
            }
        }
    }
}